package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.MaskInfo;
import model.PageBean;
import net.sf.json.JSONArray;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import util.IdUtil;
import util.JdbcUtil;
import util.JsonUtil;
import exception.SystemException;

public class MaskInfoDao {

	private static final Log log = LogFactory.getLog(MaskInfoDao.class);
	public JSONArray findAllMaskInfo(String keyword, PageBean pageBean)
			throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		int start = pageBean.getStart();
		int rows = pageBean.getRows();
		String sql = rows == 0 ?  querySql(keyword) :querySql(keyword, start, rows);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int getMaskInfoCount(String keyword) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = countSql(keyword);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
//				return rs.getInt("total");
				return rs.getInt("count");
			} else {
				return 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int deleteMaskInfo(String mids) throws SystemException {
		String sql = "delete from optim.modudf_mask where mid in(" + mids + ")";
		log.debug(sql);
		Connection conn = JdbcUtil.getAdminConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int addMaskInfo(MaskInfo maskInfo) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "insert into optim.modudf_mask(mid,owner,tab_name,tab_column,func_name,func_param,func_comment) values(?,?,?,?,?,?,?)";
		log.debug(sql);
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, IdUtil.getInstanse().getUID());
			ps.setString(2, maskInfo.getOwner());
			ps.setString(3, maskInfo.getTabName());
			ps.setString(4, maskInfo.getTabColumn());
			ps.setString(5, maskInfo.getFuncName());
			ps.setString(6, maskInfo.getFuncParam());
			ps.setString(7, maskInfo.getFuncComment());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
		
	}

	public int updateMaskInfo(MaskInfo maskInfo) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "update optim.modudf_mask m set owner=?,tab_name=?,tab_column=?,func_name=?,func_param=?,func_comment=?"
				+ " where mid=?";
		log.debug(sql);
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, maskInfo.getOwner());
			ps.setString(2, maskInfo.getTabName());
			ps.setString(3, maskInfo.getTabColumn());
			ps.setString(4, maskInfo.getFuncName());
			ps.setString(5, maskInfo.getFuncParam());
			ps.setString(6, maskInfo.getFuncComment());
			ps.setString(7, maskInfo.getMid());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	public JSONArray getMaskInfoByCondition(String owner, String tableName)
			throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "select * from optim.modudf_mask where upper(owner)='" + owner + "' and upper(tab_name)='" + tableName + "'";
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	// 拼接查询的sql语句
	private String querySql(String keyword) {
		StringBuffer sb = new StringBuffer(
				"select mid,owner,tab_name,tab_column,func_name,func_param,func_comment from optim.modudf_mask m where 1=1 ");
		sb.append(conditionSql(keyword));
		return sb.toString();
	}

	// 拼接查询码内建视图的sql语句
	private String querySql(String keyword, int start, int rows) {
		StringBuffer sb = new StringBuffer("(" + this.querySql(keyword) + ") ");
		sb.append("limit " + start + "," + rows);
		return sb.toString();
	}

	// 拼接查询总条数的sql语句
	private String countSql(String keyword) {
		StringBuffer sb = new StringBuffer(
				"select count(*) count from  optim.modudf_mask m where 1=1 ");
		sb.append(conditionSql(keyword));
		return sb.toString();
	}

	private String conditionSql(String keyword) {
		if (keyword != null && keyword.length() > 0) {
			StringBuffer sb = new StringBuffer();
			sb.append(" and m.owner like '%").append(keyword).append("%'");
			sb.append(" or m.tab_name like '%").append(keyword).append("%'");
			sb.append(" or m.tab_column like '%").append(keyword).append("%'");
			sb.append(" or m.func_name like '%").append(keyword).append("%'");
			sb.append(" or m.func_name like '%").append(keyword).append("%'");
			sb.append(" or m.func_comment like '%").append(keyword)
					.append("%'");
			return sb.toString();
		}
		return "";
	}
	
	public static void main(String[] args) {
//		new MaskInfoDao().findAllMaskInfo(null, new PageBean(1, 10));
		System.out.println(new MaskInfoDao().getMaskInfoCount(""));
	}
}
